package Server.DataBase;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
/**
 * implement all course query handler
 * */
public class CourseQuery extends Query {
	/**
	 * constractor course query
	 */
	public CourseQuery() {
		super();
	}
	/**
	 * query return course by id
	 * @param id Course
	 * @return course
	 */
	public Course getCourseById(int idCourse) throws SQLException{
		setQuery("SELECT * FROM Course " + "WHERE idCourse = '"+idCourse+"'");
		ResultSet rs = execQuery();
		if (rs.next()){
			Course course = new Course();
			course.setIdCourse(rs.getInt(1));
			course.setCourseName(rs.getString(2));
			course.setTunit(rs.getInt(3));
			course.setHours(rs.getInt(4));
			return course;
		}
		return null;
	}
	/**
	 * @return array of all courses
	 */
	public ArrayList<Course> getAllCourses() throws SQLException{
		ArrayList<Course> array = new ArrayList<Course>();
		setQuery("SELECT * FROM course");
		ResultSet rs = execQuery();
		while (rs.next()){
			Course course = new Course();
			course.setIdCourse(rs.getInt(1));
			course.setCourseName(rs.getString(2));
			course.setTunit(rs.getInt(3));
			course.setHours(rs.getInt(4));
			array.add(course);
		}
		return array;
	}
	/**
	 * method check if course name already exist
	 * @return if exist true
	 *          else false course name not exist 
	 */
	public boolean isExist(String courseName) throws SQLException{
		setQuery("SELECT * FROM Course " + "WHERE courseName = ?");
		PreparedStatement query = getPS();
		query.setString(1, courseName);
		if (query.executeQuery().next()){
			query.close();
			return true;
		}
		else {
			query.close();
			return false;
		}
	}
	/**
	 * @param init all course attribute
	 * query add a new course to course table
	 * @return true if add new course succesfule
	 *         false if course name already exists
	 */
	public boolean addCourse(int idCourse, String courseName, int tUnit, float hours, ArrayList<Course> array) throws SQLException{
		if (isExist(courseName)) return false;
		setQuery("INSERT INTO course(idCourse, courseName, tunit, hours) VALUES(?,?,?,?)");
		PreparedStatement query = getPS();
		query.setInt(1, idCourse);
		query.setString(2, courseName);
		query.setInt(3, tUnit);
		query.setFloat(4, hours);
		query.executeUpdate();
		if (array!=null)
			for (int i=0; i<array.size(); i++) 
				new PreCourseQuery().addPreCourse(array.get(i).getIdCourse(), idCourse); //init precourse fo new course //
		query.close();
		return true;
	}
	
	/**
	 * @param idStudent
	 * @return array list of all unassign courses for this students
	 */
	public ArrayList<Course> getStudentUnassignCourses(int idStudent) throws SQLException{
		boolean next;
		int i;
		ArrayList<Course> array = getAllCourses();
		for (Reg reg : new RegQuery().getStudentReg(idStudent)){
			next = true;
			i=0;
			while (next){
				if (array.get(i).getIdCourse()==reg.getIdcourse()){
					array.remove(i);
					next=false;
				}
				else i++;
			}
		}
		return array;
	}
	/**
	 * 
	 * @return course table
	 */
	public ResultSet getTable() throws SQLException{
		setQuery("SELECT * FROM course");
		return execQuery();
	}
}
